const connection = require('../app/database');

class WorkerService {
  // 获取数据列表
  async getWorker(name, identify, repairType, page, size) {

    try {
      // 将分页变为整形
      page = parseInt(page) || 1;
      size = size || 10;

      // 拼接sql语句、构建传入connection.execute中的数据参数
      let dataObj = { name, identify, repairType }; // 将传入的参数组合成一个对象
      let sql = '', dataArr = []; // 定义拼接的sql和传入数据的变量
      for (let key in dataObj) {
        if (dataObj[key] !== '') {  // 当params中传入的参数不为空
          sql += ` AND ${key} = ?`; // 拼接sql
          dataArr.push(dataObj[key]); // 构建插入的数据
        }
      }
      sql += ' LIMIT ?, ?';
      dataArr.push(((page - 1) * size).toString(), size);

      // 执行sql语句查询数据
      const statement = `
        SELECT 
        w.id, w.name, w.identify, w.age, w.bornFrom, w.phone, w.workTime, t.work repairType, benefit, finish
        FROM worker w
        LEFT JOIN workType t ON w.repairType = t.id
        WHERE 1=1 AND valid = 1${sql};
      `;
      const [result] = await connection.execute(statement, dataArr);
      console.log('[worker.service]获取员工数据', result);

      return result;
      
    } catch(e) {
      console.log(e);
    }
  }

  // 获取列表数据总数
  async getWorkerCount() {
    const statement = `
      SELECT COUNT(*) AS total FROM worker WHERE valid = 1;
    `;
    const [result] = await connection.execute(statement);
    console.log('[worker.service]获取worker总数', result);
    return result[0];
  }

  // 添加员工数据
  async addWorker(arg) {
    try {
      let dataArr = [], sqlArr = [], sql = '';
      for (let key in arg) {
        sqlArr.push(key);
        dataArr.push(arg[key]);
      }
      sql = sqlArr.join(',');
      const statement = `
        INSERT INTO worker (${sql})
          VALUES (?, ?, ?, ?, ?, ? ,?, ?, ?, ?);
      `;
      const [result] = await connection.execute(statement, dataArr);
      console.log('[worker.service]获取添加员工结果', result);
      return true;
    } catch(e) {
      console.log('[worker.service]添加员工失败', e);
    }
  }

  // 编辑员工数据
  async updateWorker(arg) {
    try {
      const { id, age, phone, bornFrom } = arg;
      const statement = `
        UPDATE worker SET age = ?, phone = ?, bornFrom = ? WHERE id = ?;
      `;
      const [result] = await connection.execute(statement, [age, phone, bornFrom, id]);
      console.log('[worker.service]修改员工信息', result);
      return true;
    } catch(e) {
      console.log('[worker.service]修改员工失败', e);
    }
  }

  // 失效员工数据
  async deleteWorker(arg) {
    try {
      const { id } = arg;
      const statement = `
        UPDATE worker SET valid = 0 WHERE id = ?;
      `;
      const [result] = await connection.execute(statement, [id]);
      console.log('[worker.service_deleteWorker]{success}失效员工列表', result);
      return true;
    } catch(e) {
      console.log('[[worker.service_deleteWorker]{fail}失效员工列表', e);
    }
  }

  // 获取员工按照订单完成量排序的数据列表(前10位)
  async getWorkerListSortFinish() {
    try {
      const statement = `
        SELECT 
        w.id, w.name, w.phone, t.work repairType, finish
        FROM worker w
        LEFT JOIN workType t ON w.repairType = t.id
        ORDER BY finish DESC LIMIT 0, 10;
      `;
      const [result] = await connection.execute(statement);
      console.log('[worker.service_getWorkerListSortFinish]{success}', result);
      return result;
    } catch (e) {
      console.log('[worker.service_getWorkerListSortFinish]{fail}', e);
    }
  }

  // 获取员工按照盈利金额排序的数据列表(前10位)
  async getWorkerListSortBenefit() {
    try {
      const statement = `
      SELECT 
        w.id, w.name, w.phone, t.work repairType, benefit
        FROM worker w
        LEFT JOIN workType t ON w.repairType = t.id
        ORDER BY benefit DESC LIMIT 0, 10;
      `;
      const [result] = await connection.execute(statement);
      console.log('[worker.service_getWorkerListSortBenefit]{success}', result);
      return result;
    } catch(e) {
      console.log('[worker.service_getWorkerListSortBenefit]{fail}', e);
    }
  }
}

module.exports = new WorkerService();